﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using CaseXmlService;

namespace FantanstPlatform.Decision.CaseXmlService
{
    public class CaseCountFactory
    {
        DateTime startTime = DateTime.Now;
        DateTime endTime = DateTime.Now;
        DateTime now = DateTime.Now;
        int year = DateTime.Now.Year;
        int month = DateTime.Now.Month;
        int day = DateTime.Now.Day;

        /// <summary>
        /// 通过时间段获得案件数
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        private List<CaseCount> getCaseCount(DateTime startTime, DateTime endTime)
        {
            List<CaseCount> caseCounts = new List<CaseCount>();
            string[] policeStations = new string[] { "太升", "草市", "新华", "黄瓦", "西御", "汪家", "府南", "草堂", "光华", "东坡", "金沙", "苏坡", "黄田", "文家", "治大", "刑大" };
            for (int i = 0; i < policeStations.Length; i++)
            {
                caseCounts.Add(new CaseCount() { Station = policeStations[i], CriminalCount = 0, SecurityCount = 0, TotalCount = 0 });
            }

            string connStr = ConfigurationManager.AppSettings["Case"];
            //建立连接
            SqlConnection conn = new SqlConnection(connStr);
            //建立查询命令
            SqlCommand myCommand = new SqlCommand("P_Statistics", conn);
            //调用存储过程名
            myCommand.CommandType = CommandType.StoredProcedure;

            //设置存储过程的参数值,其中@id 为存储过程的参数.
            SqlParameter PoliceStation = myCommand.Parameters.Add("@PoliceStation", SqlDbType.NText);
            PoliceStation.Value = "青羊区";
            SqlParameter BeginTime = myCommand.Parameters.Add("@BeginTime", SqlDbType.NText);
            BeginTime.Value = startTime;
            SqlParameter EndTime = myCommand.Parameters.Add("@EndTime", SqlDbType.NText);
            EndTime.Value = endTime;
            conn.Open();
            //执行命令
            SqlDataReader reader = myCommand.ExecuteReader();

            while (reader.Read())
            {
                for (int i = 0; i < policeStations.Length; i++)
                {
                    if (reader[0].ToString() == caseCounts[i].Station)
                    {
                        int rcount = Convert.ToInt32(reader[2]);
                        if (reader[1].ToString() == "行政案件")
                        {
                            caseCounts[i].CriminalCount = rcount;
                            caseCounts[i].TotalCount = caseCounts[i].SecurityCount + rcount;
                        }
                        else if (reader[1].ToString() == "刑事案件")
                        {
                            caseCounts[i].SecurityCount = rcount;
                            caseCounts[i].TotalCount = caseCounts[i].CriminalCount + rcount;
                        }
                        break;
                    }
                }
            }
            conn.Close();
            return caseCounts;
        }
        /// <summary>
        /// 获得本年案件数
        /// </summary>
        /// <returns></returns>
        public List<CaseCount> getThisYearCase(out DateTime outStartTime , out DateTime outEndTime)
        {
            DateTime keyDate = new DateTime(now.Year, 12, 21);
            if (keyDate > now)
            {
                startTime = new DateTime(now.Year - 1, 12, 21);
            }
            else
            {
                startTime = new DateTime(now.Year, 12, 21);
            }
            endTime = DateTime.Now;
            outStartTime = startTime;
            outEndTime = endTime;
            return getCaseCount(startTime, endTime);
        }
        /// <summary>
        /// 获得本月案件数
        /// </summary>
        /// <returns></returns>
        public List<CaseCount> getThisMonthCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            if (month == 1)
            {
                if (day >= 21)
                    startTime = new DateTime(now.Year, month, 21);
                else
                    startTime = new DateTime(now.Year - 1, 12, 21);
            }
            else
            {
                if (day >= 21)
                    startTime = new DateTime(now.Year, month, 21);
                else
                    startTime = new DateTime(now.Year, month - 1, 21);
            }
            endTime = DateTime.Now;
            outStartTime = startTime;
            outEndTime = endTime;
            return getCaseCount(startTime, endTime);
        }
        /// <summary>
        /// 获得本周案件数
        /// </summary>
        /// <returns></returns>
        public List<CaseCount> getThisWeekCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            if (Convert.ToInt32(DateTime.Now.DayOfWeek) == 0)
                startTime = now.Date.AddDays(-6);
            else
                startTime = now.Date.AddDays(-Convert.ToInt32(now.DayOfWeek) + 1);
            endTime = DateTime.Now;
            outStartTime = startTime;
            outEndTime = endTime;
            return getCaseCount(startTime, endTime);
        }
        /// <summary>
        /// 获得上月案件数
        /// </summary>
        /// <returns></returns>
        public List<CaseCount> getLastMonthCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            if (day >= 21)
            {
                endTime = new DateTime(year, month, 20);
                if (month == 12)
                    startTime = new DateTime(year - 1, 12, 21);
                else
                    startTime = new DateTime(year, month - 1, 21);
            }
            else
            {
                if (month == 12)
                {
                    endTime = new DateTime(year - 1, 12, 20);
                    startTime = new DateTime(year - 1, 11, 21);
                }
                else
                {
                    endTime = new DateTime(year, month - 1, 20);
                    startTime = new DateTime(year, month - 2, 21);
                }
            }
            outStartTime = startTime;
            outEndTime = endTime;
            return getCaseCount(startTime, endTime);
        }
        /// <summary>
        /// 获得上周案件数
        /// </summary>
        /// <returns></returns>
        public List<CaseCount> getLastWeekrCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            if (Convert.ToInt32(DateTime.Now.DayOfWeek) == 0)
            {
                startTime = now.Date.AddDays(-13);
                endTime = now.Date.AddDays(-7);
            }
            else
            {
                startTime = now.Date.AddDays(-Convert.ToInt32(now.DayOfWeek) - 6);
                endTime = now.Date.AddDays(-Convert.ToInt32(now.DayOfWeek));
            }
            outStartTime = startTime;
            outEndTime = endTime;
            return getCaseCount(startTime, endTime);
        }
        /// <summary>
        /// 获得五类案件
        /// </summary>
        /// <returns></returns>
        public List<ContrastCaseCount> getFiveCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            List<ContrastCaseCount> counts = new List<ContrastCaseCount>();

            DateTime keyDate = new DateTime(now.Year, 12, 21);
            if (keyDate > now)
                startTime = new DateTime(now.Year - 1, 12, 21);
            else
                startTime = new DateTime(now.Year, 12, 21);

            endTime = DateTime.Now;
            string connStr = ConfigurationManager.AppSettings["CaseDW"];
            string sqlStr = "select count(b.[TypeName]),b.[TypeName], Biz_Year+'-'+c.Biz_Month from dbo.FactCase as a ,dbo.DimCategoryType as b ,dbo.DimDate as c ";
            sqlStr += " where a.DimCategoryTypeId = b.id and a.DimDateId = c.id and (b.[TypeName] = '盗窃汽车案' or b.[TypeName] = '诈骗案'or b.[TypeName] = '抢劫案' or b.[TypeName] = '抢夺案'or b.[TypeName] = '入室盗窃案') and (c.rq between '" + startTime + "' and '" + endTime + "') ";
            sqlStr += "group by b.[TypeName],Biz_Year+'-'+c.Biz_Month order by cast((Biz_Year+'-'+c.Biz_Month+'-'+'1') as Datetime)";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand myCommand = new SqlCommand(sqlStr, conn);
            conn.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            while (reader.Read())
            {
                bool exsit = false;
                int key = -1;
                for (int i = 0; i < counts.Count; i++)
                {
                    if (reader[2].ToString() == counts[i].Time)
                    {
                        key = i;
                        exsit = true;
                        break;
                    }
                }
                if (!exsit)
                {
                    ContrastCaseCount count = new ContrastCaseCount();
                    count.Time = reader[2].ToString();
                    switch (reader[1].ToString())
                    {
                        case "盗窃汽车案":
                            count.CaseCount1 = reader[0].ToString();
                            break;
                        case "诈骗案":
                            count.CaseCount2 = reader[0].ToString();
                            break;
                        case "抢劫案":
                            count.CaseCount3 = reader[0].ToString();
                            break;
                        case "抢夺案":
                            count.CaseCount4 = reader[0].ToString();
                            break;
                        case "入室盗窃案":
                            count.CaseCount5 = reader[0].ToString();
                            break;
                    }
                    counts.Add(count);
                }
                else 
                {
                    switch (reader[1].ToString())
                    {
                        case "盗窃汽车案":
                            counts[key].CaseCount1 = reader[0].ToString();
                            break;
                        case "诈骗案":
                            counts[key].CaseCount2 = reader[0].ToString();
                            break;
                        case "抢劫案":
                            counts[key].CaseCount3 = reader[0].ToString();
                            break;
                        case "抢夺案":
                            counts[key].CaseCount4 = reader[0].ToString();
                            break;
                        case "入室盗窃案":
                            counts[key].CaseCount5 = reader[0].ToString();
                            break;
                    }
                }
            }
            outStartTime = startTime;
            outEndTime = endTime;
            return counts;
        }
        /// <summary>
        /// 获得刑事案件和行政案件数据
        /// </summary>
        /// <returns></returns>
        public List<ContrastCaseCount> getTwoCase(out DateTime outStartTime, out DateTime outEndTime)
        {
            List<ContrastCaseCount> counts = new List<ContrastCaseCount>();

            DateTime keyDate = new DateTime(now.Year, 12, 21);
            if (keyDate > now)
                startTime = new DateTime(now.Year - 1, 12, 21);
            else
                startTime = new DateTime(now.Year, 12, 21);

            endTime = DateTime.Now;
            string connStr = ConfigurationManager.AppSettings["CaseDW"];
            
            string sqlStr = "select count(b.[CategoryName]),b.[CategoryName], Biz_Year+'-'+c.Biz_Month from dbo.FactCase as a ,dbo.DimCategoryType as b ,dbo.DimDate as c ";
            sqlStr += "where a.DimCategoryTypeId = b.id and a.DimDateId = c.id and (c.rq between '" + startTime + "' and '" + endTime + "') ";
            sqlStr += "group by b.[CategoryName],Biz_Year+'-'+c.Biz_Month order by cast((Biz_Year+'-'+c.Biz_Month+'-'+'1') as Datetime)";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand myCommand = new SqlCommand(sqlStr, conn);
            conn.Open();
            SqlDataReader reader = myCommand.ExecuteReader();
            while (reader.Read())
            {
                bool exsit = false;
                int key = -1;
                for (int i = 0; i < counts.Count; i++)
                {
                    if (reader[2].ToString() == counts[i].Time)
                    {
                        key = i;
                        exsit = true;
                        break;
                    }
                }
                if (!exsit)
                {
                    ContrastCaseCount count = new ContrastCaseCount();
                    count.Time = reader[2].ToString();
                    switch (reader[1].ToString())
                    {
                        case "行政案件":
                            count.CaseCount1 = reader[0].ToString();
                            break;
                        case "刑事案件":
                            count.CaseCount2 = reader[0].ToString();
                            break;
                    }
                    counts.Add(count);
                }
                else
                {
                    switch (reader[1].ToString())
                    {
                        case "行政案件":
                            counts[key].CaseCount1 = reader[0].ToString();
                            break;
                        case "刑事案件":
                            counts[key].CaseCount2 = reader[0].ToString();
                            break;
                    }
                }
            }
            outStartTime = startTime;
            outEndTime = endTime;
            return counts;
        }
    }
}
